﻿CREATE PROCEDURE [acms].[Post_Put]
	@Id int, 
	@SiteId int, 
	@VersionId int,
	@ParentId int, 
	@Name varchar(100), 
	@DisplayName varchar(100), 
	@TemplateId int, 
	@ThumbnailId int,
	@Summary varchar(2000), 
	@Sequence int, 
	@Tags varchar(2000),
	@PostCreated datetime, 
	@VersionCreated datetime, 
	@StartDate datetime, 
	@EndDate datetime, 
	@ConcurencyId rowversion,
	@IdOut int output, 
	@SiteNameOut varchar(50) output, 
	@VersionIdOut int output,
	@ParentPathOut varchar(255) output, 
	@TemplateNameOut varchar(255) output, 
	@SequenceOut int output, 
	@ConcurencyIdOut rowversion output
AS
	if @Id = 0
		set @Id = null
	if @ParentId = 0
		set @ParentId = null
	if @VersionId = 0
		set @VersionId = null
	
	
	if @Id is null begin
		insert into Posts
			(SiteId, Created)
		values
			(@SiteId, @PostCreated)
		set @IdOut = @@Identity
	end
	else 
		select @IdOut = @Id from [Posts] where Id = @Id and Deleted = 0
		
	--if @IdOut is null can't find record to update so escape out
	if @IdOut is null begin
		return -200
	end
	
	declare @ParentSequence varchar(50)
	declare @ParentVersionId int
	
	if @StartDate is null and @EndDate is null begin
		--get pending parent id if it exists
		select @ParentVersionId = Id, @ParentPathOut = isNull(ParentPath,'') + Name + '/', @ParentSequence = isNull(ParentSequence,'')  + cast(Sequence as varchar(3)) From [PostVersions] where PostId = @ParentId and StartDate is null and EndDate is null
	end
	
	if @ParentVersionId is null begin
		-- get from live version
		select @ParentVersionId = Id, @ParentPathOut = isNull(ParentPath,'') + Name + '/', @ParentSequence = isNull(ParentSequence,'')  + cast(Sequence as varchar(3)) From [PostVersions] where PostId = @ParentId and StartDate is not null and EndDate is null
	end
	
	if exists (select null from [PostVersions] where Id = @VersionId) begin
		declare @OldName varchar(255)
		declare @OldParentId int
		declare @OldParentPath varchar(255)
		declare @OldSequence int
		select @OldParentId = ParentId, @OldParentPath = ParentPath, @OldName = Name, @OldSequence = Sequence From [PostVersions] Where Id = @VersionId
		
		update [PostVersions]
		set
			ParentId = @ParentId,
			ParentPath = @ParentPathOut,
			Name = @Name,
			DisplayName = @DisplayName,
			TemplateId = @TemplateId,
			ThumbnailId = @ThumbnailId,
			Summary = @Summary,
			Tags = @Tags,
			Sequence = @Sequence,
			ParentSequence = @ParentSequence,
			StartDate = @StartDate,
			EndDate = @EndDate
		where
			Id = @VersionId
			and ConcurencyId = @ConcurencyId
		if @@RowCount <> 1 
			return -100
		else
			set @VersionIdOut = @VersionId
			
		--Update any old child parent urls
		if @OldName <> @Name or @OldParentId <> @ParentId begin
			update [PostVersions]
				set ParentPath = Replace(ParentPath, isNull(@OldParentPath, '') + @OldName + '/', isNull(@ParentPathOut, '') + @Name + '/')
			from [PostVersions] a inner join [Posts] b on a.PostId = b.Id
			where b.SiteId = @SiteId 
				and a.ParentPath like isNull(@OldParentPath, '') + @OldName + '/%'
		end
		if @OldSequence <> @Sequence begin
			Update [PostVersions]
				set ParentSequence = IsNull(@ParentSequence, '') + Cast(@Sequence as varchar(3)) + SubString(ParentSequence, Len(@ParentSequence) + 1, Len(ParentSequence) - Len(@ParentSequence) - 1)
			from [PostVersions] a inner join [Posts] b on a.PostId = b.Id
			where b.SiteId = @SiteId
				and a.ParentPath like isNull(@ParentPathOut, '') + @Name +  '/%'
		end
	end
	else if @VersionId is null begin
		select @Sequence = isNull(Max(Sequence) + 1, 0) from [PostVersions] a where a.ParentId = @ParentId	
		
		insert [PostVersions]
			(PostId, ParentId, ParentPath, Name, DisplayName, Sequence, ParentSequence, TemplateId, ThumbnailId, Summary, Tags, Created, StartDate, EndDate)
		values
			(@IdOut, @ParentId, @ParentPathOut, @Name, @DisplayName, @Sequence, @ParentSequence, @TemplateId, @ThumbnailId, @Summary, @Tags, @VersionCreated, @StartDate, @EndDate)
		
		set @VersionIdOut = @@Identity
	end
	else begin
		return -200
	end
	
	select 
		@SiteNameOut = SiteName,
		@TemplateNameOut = TemplateName,
		@SequenceOut = Sequence,
		@ConcurencyIdOut = ConcurencyId
	from Post_View
	where Id = @IdOut
	
	RETURN 0;
